package com.shuhe.handler;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.shuhe.JettyServer;
import com.shuhe.domain.mxgc.CommonResponseBody;
import com.shuhe.domain.report.ReportDataResponse;
import com.shuhe.domain.report.ReportStructResponse;
import com.shuhe.metastore.DBSql;
import com.shuhe.metastore.MetaMysql;
import org.apache.log4j.Logger;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Set;

import static com.shuhe.common.CommonFunction.*;


public class ReportHandler extends HttpServlet {

    private static Logger logger = Logger.getLogger(ReportHandler.class);

    @Override
    protected void doOptions(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp = setResponse(resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        try {
//            if (!ClientInterceptor.preHandle(req, resp)) {
//                throw new Exception("非法客户端访问");
//            }
            req.setCharacterEncoding("UTF-8");
            resp = setResponse(resp);
            String bodyStr = readAsChars(req);
            JSONObject bodyJson = JSONObject.parseObject(bodyStr);
            String id = bodyJson.getString("id");
            String operate = bodyJson.getString("operate");
            String isOperate = "";
            if (bodyJson.getString("isOperate") != null) {
                isOperate = bodyJson.getString("isOperate");
            }
            String operateWidth = "60";
            if (bodyJson.getString("operateWidth") != null) {
                operateWidth = bodyJson.getString("operateWidth");
            }
            CommonResponseBody commonResp = new CommonResponseBody();
            commonResp.setSuccess();
            Connection metaConn = null;
            Connection dbConn = null;
            try {
                metaConn = MetaMysql.connect();
                String sql = "select id, report_name, db_name, sql_content as table_name, option_column, db_type, " +
                        "'mxgc' as source" +
                        " from mxgc_publish " +
                        "where id = '" + id + "'\n" +
                        "union\n" +
                        "select id, report_name, db_name, table_name, option_column, '' as db_type, 'full' as source" +
                        " from full_report " +
                        "where id = '" + id + "'";
                ResultSet rs = MetaMysql.querySql(metaConn, sql);
                ReportStructResponse reportResponse = new ReportStructResponse();
                if (rs.next()) {
                    String reportName = rs.getString("report_name");
                    String dbName = rs.getString("db_name");
                    String tableName = rs.getString("table_name");
                    String optionColumn = rs.getString("option_column");
                    String dbType = rs.getString("db_type");
                    String source = rs.getString("source");
                    reportResponse.setReportName(reportName);
                    reportResponse.setOptionColumn(optionColumn);
                    reportResponse.setDbName(dbName);
                    reportResponse.setTableName(tableName);
                    reportResponse.setDbType(dbType);
                    reportResponse.setSource(source);
                } else{
                    //没有数据，直接返回成功
                    reportResponse.setCommon(commonResp);
                    String respJson = JSONObject.toJSONString(reportResponse);
                    resp.getWriter().write(respJson);
                    return;
                }
                if (operate.equals("struct")) {
                    //得到结构
                    if (isOperate.equals("true")) {
                        reportResponse = makeOptionColumn(reportResponse, true, operateWidth);
                    } else {
                        reportResponse = makeOptionColumn(reportResponse, false, operateWidth);
                    }
                    reportResponse.setCommon(commonResp);
                    String respJson = JSONObject.toJSONString(reportResponse);
                    resp.getWriter().write(respJson);
                }
                if (operate.equals("fullTextStruct")) {
                    sql = "select show_header from full_report where id = '" + id + "'";
                    rs = MetaMysql.querySql(metaConn, sql);
                    rs.next();
                    boolean showHeader = rs.getBoolean("show_header");
                    //得到全文检索所用报表结构
                    reportResponse = makeOptionColumn(reportResponse, showHeader, operateWidth);
                    reportResponse.setCommon(commonResp);
                    String respJson = JSONObject.toJSONString(reportResponse);
                    resp.getWriter().write(respJson);
                }
                if (operate.equals("data")) {
                    //得到数据
                    HashMap<String, String> maps = getColumnType(reportResponse);
                    JSONObject sort = bodyJson.getJSONObject("sort");
                    String orderStr = "";
                    for (String columnStr : sort.keySet()) {
                        String value = sort.getString(columnStr);
                        if (value.equals("ascend")) {
                            value = " asc ";
                        } else {
                            value = " desc ";
                        }
                        String valueType = getValueType(columnStr, reportResponse.getOptionColumn());
                        if (valueType.equals("digit")) {
                            orderStr = " order by " + columnStr + " + 0 " + value;
                        } else {
                            orderStr = " order by " + columnStr + value;
                        }

                    }
                    JSONObject params = bodyJson.getJSONObject("params");
                    String current = params.getString("current");
                    String pageSize = params.getString("pageSize");
                    Set<String> sets = params.keySet();
                    sets.remove("current");
                    sets.remove("pageSize");
                    String whereStr = "";
                    for (String tmpstr : sets) {
                        String value = "";
                        if (tmpstr.equals("biaoqianmulu")) {
                            whereStr = whereStr + params.getString(tmpstr) + "    ";
                        } else {
                            String colType = fromMapsGetType(maps, tmpstr);
                            if (colType.equals("dateTime")) {
                                String one = params.getJSONArray(tmpstr).getString(0);
                                String two = params.getJSONArray(tmpstr).getString(1);
                                whereStr = whereStr + tmpstr + " >= '" + one
                                        + "' and " + tmpstr + " <= '" + two + "' and ";
                            } else {
                                if (colType.equals("digit")) {
                                    String one = params.getJSONArray(tmpstr).getString(0);
                                    String two = params.getJSONArray(tmpstr).getString(1);
                                    whereStr = whereStr + tmpstr + " >= " + one
                                            + " and " + tmpstr + " <= " + two + " and ";
                                } else {
                                    value = params.getString(tmpstr);
                                    if (value.length() > 0) {
                                        if (reportResponse.getSource().equals("full")) {
                                            whereStr = whereStr + tmpstr + " = '" + value + "' and ";
                                        }
                                        whereStr = whereStr + tmpstr + " like '%" + value + "%' and ";
                                    }
                                }
                            }
                        }
                    }
                    if (whereStr.length() > 0) {
                        whereStr = whereStr.substring(0, whereStr.length() - 4);
                    }
                    String tableName = "select * from (" + reportResponse.getTableName() + ") v_table";


                    String operateStr = bodyJson.getString("operateDetail");
                    String resultStr = "";
                    if (operateStr != null) {
                        //?userId=ry&operate=3&tableEName=ods_klj_sjb_lg&id=1301050404202011150020
                        String userName = bodyJson.getString("userName");
                        String dbDriver = JettyServer.globalProperties.getProperty("db.driver");
                        if (dbDriver.indexOf(".postgresql.") >= 0) {
                            operateStr = "'" +
                                    JettyServer.globalProperties.getProperty("fullText.detail.url") +
                                    "?userId=" + userName + "&operate=3&tableEName="
                                    + tableName.split("\\.")[1]
                                    + "&id=' || yz_jrid";
                        } else {
                            operateStr = "concat_ws('', '" +
                                    JettyServer.globalProperties.getProperty("fullText.detail.url") +
                                    "?userId=" + userName + "&operate=3&tableEName="
                                    + tableName.split("\\.")[1]
                                    + "&id=', yz_jrid)";
                        }
                        if (orderStr.length() <= 0) {
                            //增加排序字段
                            sql = "select order_col from full_table where id = '" + id + "'";
                            rs = MetaMysql.querySql(metaConn, sql);
                            rs.next();
                            orderStr = " order by " + rs.getString("order_col") + " desc";
                        }


                        resultStr = makeData(id, tableName, reportResponse,
                                pageSize, current, whereStr, orderStr, operateStr);
                    } else {
                        resultStr = makeData(id, tableName, reportResponse,
                                pageSize, current, whereStr, orderStr, "");
                    }
                    resp.getWriter().write(resultStr);
                }

            } finally {
                closeDb(metaConn);
                closeDb(dbConn);
            }
        } catch (Exception ex) {
            resp.getWriter().write(returnFail(ex.getMessage()));
            ex.printStackTrace();
        }
    }

    public static String fromMapsGetType(HashMap<String, String> maps, String tmpstr) {
        String tmpstr1 = maps.get(tmpstr);
        if (tmpstr1 == null) {
            tmpstr1 = "";
        }
        return tmpstr1;
    }

    public static String makeData(String id, String tableName, ReportStructResponse reportResponse, String pageSize,
                                  String current, String whereStr, String orderStr,
                                  String operateStr) throws Exception {
        long beginTime = Calendar.getInstance().getTimeInMillis();
        String optionColumn = reportResponse.getOptionColumn();
        Connection conn = null;
        try {
            if (reportResponse.getDbType().equals("meta")) {
                conn = MetaMysql.connect();
            } else {
                conn = DBSql.connect();
            }
            JSONArray ja = JSONObject.parseArray(JSONObject.parseObject(optionColumn).getString("column"));
            String columnStr = "";
            for (int k = 0; k < ja.size(); k++) {
                JSONObject jo = ja.getJSONObject(k);
                columnStr = columnStr + (String) jo.get("dataIndex") + ",";
            }
            columnStr = columnStr.substring(0, columnStr.length() - 1);
            if (operateStr.length() > 0) {
                columnStr = columnStr + " , " + operateStr + " as operate_string_url";
            }
            String sql = "select " + columnStr + " from (" + tableName + ") v_table";
            if (whereStr.length() > 0) {
                sql = sql + " where " + whereStr;
            }
            if (orderStr.length() > 0) {
                sql = sql + orderStr;
            }

            String countSql = "select count(*) as cun from (" + sql + ") a";
            //返回行数
            int beginNum = (Integer.parseInt(current) - 1) * Integer.parseInt(pageSize);
            //判断数据库类型
            if (conn.getClass().getName().equals("org.postgresql.jdbc.PgConnection")) {
                sql = sql + " limit " + pageSize + " offset" +
                        " " + String.valueOf(beginNum);
            } else {
                sql = sql + " limit " + String.valueOf(beginNum) + ", " + pageSize;
            }
            return makeDataResp(conn, sql, current, beginTime, countSql);
        } finally {
            closeDb(conn);
        }
    }

    public static String makeDataResp(Connection conn, String sql, String current, long beginTime, String countSql) throws Exception {
        ResultSet rs = DBSql.querySql(conn, sql);
        ArrayList<Object> datas = getResultSet(rs);
        ReportDataResponse resp = new ReportDataResponse();
        resp.setSuccess(true);
        resp.setPage(Integer.parseInt(current));

        rs = DBSql.querySql(conn, countSql);
        int total = 0;
        if (rs.next()) {
            total = rs.getInt("cun");
        }
        resp.setTotal(total);
        resp.setData(datas);
        resp.setCommon(getCommonResponseBody(beginTime));
        String respJson = JSONObject.toJSONString(resp);
        return respJson;
    }

    public static String getValueType(String columnStr, String optionColumn) {
        JSONArray ja = JSONObject.parseArray(JSONObject.parseObject(optionColumn).getString("column"));
        for (int k = 0; k < ja.size(); k++) {
            JSONObject jo = ja.getJSONObject(k);
            if (jo.get("dataIndex").toString().equals(columnStr)) {
                if (jo.get("valueType") == null) {
                    return "";
                } else {
                    return jo.get("valueType").toString();
                }
            }
        }
        return "";
    }

    public static HashMap<String, String> getColumnType(ReportStructResponse reportStructResponse) throws Exception {
        String optionColumn = reportStructResponse.getOptionColumn();
        JSONArray ja = JSONObject.parseArray(JSONObject.parseObject(optionColumn).getString("column"));
        HashMap<String, String> maps = new HashMap<>();
        for (int k = 0; k < ja.size(); k++) {
            JSONObject jo = ja.getJSONObject(k);
            String valueType = (String) jo.get("valueType");
            if (valueType == null) {
                valueType = "text";
            }
            String dataIndex = (String) jo.get("dataIndex");
            maps.put(dataIndex, valueType);
        }
        return maps;
    }


    public static ReportStructResponse makeOptionColumn(ReportStructResponse reportStructResponse,
                                                        boolean isOperate, String operateWidth) throws Exception {
        String optionColumn = reportStructResponse.getOptionColumn();
        JSONArray ja = JSONObject.parseArray(JSONObject.parseObject(optionColumn).getString("column"));
        String columnConfig = "[";
        for (int k = 0; k < ja.size(); k++) {
            JSONObject jo = ja.getJSONObject(k);
            String sql = (String) jo.get("sql");
            String search = (String) jo.get("search");
            if (search == null) {
                search = "false";
            }
            String sorter = (String) jo.get("sorter");
            if (sorter == null) {
                sorter = "false";
            }
            Object valueEnum = jo.get("valueEnum");
            Object fieldProps = jo.get("fieldProps");
            String valueType = (String) jo.get("valueType");
            if (valueType == null) {
                valueType = "text";
            }
            String hideInTable = (String) jo.get("hideInTable");
            if (hideInTable == null) {
                hideInTable = "false";
            }
            String width = (String) jo.get("width");
            columnConfig = columnConfig + "{\"title\": \"" + jo.get("title") + "\"," +
                    "\"dataIndex\": \"" + jo.get("dataIndex") + "\"," +
                    "\"search\": " + search + "," +
                    "\"sorter\": " + sorter + "," +
                    "\"hideInTable\": " + hideInTable + "," +
                    "\"ellipsis\": true," +
                    "\"valueType\": \"" + valueType + "\"";

            if (width != null) {
                columnConfig = columnConfig + ",\"width\":" + width;
            }
            if (valueEnum != null) {
                columnConfig = columnConfig + ",\"valueEnum\":" + valueEnum.toString();
            }
            if (fieldProps != null) {
                columnConfig = columnConfig + ",\"fieldProps\":" + fieldProps.toString();
            }
            if (sql != null && sql.length() > 0) {
                Connection conn = null;
                try {
                    if (reportStructResponse.getDbType().equals("meta")) {
                        conn = MetaMysql.connect();
                    } else {
                        conn = DBSql.connect();
                    }
                    ResultSet rs = DBSql.querySql(conn, sql);
                    columnConfig = columnConfig + ",\"valueEnum\": {";
                    boolean isExist = false;
                    while (rs.next()) {
                        String mapkey = rs.getString("mapkey");
                        String mapname = rs.getString("mapname");
                        columnConfig = columnConfig + "\"" + mapkey + "\":{\"text\":\"" + mapname + "\"},";
                        isExist = true;
                    }
                    if (isExist) {
                        columnConfig = columnConfig.substring(0, columnConfig.length() - 1);
                    }
                    columnConfig = columnConfig + "}";

                } finally {
                    closeDb(conn);
                }
            }
            columnConfig = columnConfig + "},";
        }
        columnConfig = columnConfig.substring(0, columnConfig.length() - 1);

        String operateStr = ",{\n" +
                "\"title\": \"操作\",\n" +
                "\"width\": " + operateWidth + "," +
                "\"valueType\": \"option\",\n" +
                "\"key\": \"option\",\n" +
                "\"render\": \"\"" +
                "}";
        if (!isOperate) {
            operateStr = "";
        }
        columnConfig = columnConfig + operateStr + "]";
        reportStructResponse.setOptionColumn(columnConfig);
        return reportStructResponse;
    }

}
